/************************************************************************
   This CREATE statement is used only for compiling the procedure, that
   is why it is contained Before the create procedure statement.  The
   temporary table at execution time is created in p280_p006.
 ************************************************************************/

create table #t280_fypd	
( f_invtyp_c   char(14) null
, f_invtypnm_x char(30) null
, f_inv1_q     money null
, f_inv1_a     money null
, f_inv2_q     money null 
, f_inv2_a     money null
)      
go
drop proc p280_p006e
go

create procedure p280_p006e 
/*
  -----------------------------------------------------------------------
      P280_P006E - Inventory Analysis Report - DETAIL TABLE t280lot.
  -----------------------------------------------------------------------
*/

/*
    ------  INPUT VARIABLES   ------
*/
   @bpd char(6)   = " " 
 , @epd char(6)   = " "
 , @loc char(3)   = " "
 , @div char(3)   = " " 
 , @dpt char(4)   = " "
 , @inv char(4)   = " "
 , @src char(6)   = " "
 , @stg char(3)   = " "
 , @fop char(6)   = " "
 , @afm char(3)   = " "
 , @srt char(15)  = " "
 , @act char(6)   = " "
 , @dev char(15)  = " "
 , @spc char(8)   = " "
 , @exp char(20)  = " None"
 , @ord char(15)  = " Category"
 , @grp char(30)  = " "
 , @dvn char(30)  = " "
 , @bus char(30)  = " "
 , @glv char(1)   = "V"
 , @lot char(16)  = " "

with recompile
as
/*
    ------ INTERNAL VARIABLES ------
*/

/*
    ------     MAIN LOGIC     ------
*/

/***********************************************************************
   Control Section for the Detail Reports
 ***********************************************************************/

if @grp != '%'
or @dvn != '%'
or @bus != '%'
   goto RptByGrpDivBus


RptBySummary:

begin
insert #t280_fypd 
select '    '  
     , b.f_lot_n
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280lot  b                                                   
 where b.f_fypd      = @bpd
   and b.f_lot_n     like @lot
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
/* and b.f_glval_i   like @glv */
 group by b.f_lot_n  
union all
select '    '  
     , b.f_lot_n
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280lot  b                                                   
 where b.f_fypd      = @epd
   and b.f_lot_n     like @lot
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
/* and b.f_glval_i   like @glv */
 group by b.f_lot_n  
end

   goto ENDIT


RptByGrpDivBus:

begin
insert #t280_fypd 
select '    '  
     , b.f_lot_n
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
     , 0
     , $0.00
  from d280db1.dbo.t280lot  b                                                   
     , d280db1.dbo.t280org  c                                                   
 where b.f_fypd      = @bpd
   and c.f_afm_c     = b.f_afm_c
   and c.f_afm_c     like @afm
   and c.f_grp_x     like @grp
   and c.f_div_x     like @dvn
   and c.f_bu_x      like @bus
   and b.f_lot_n     like @lot
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
/* and b.f_glval_i   like @glv */
 group by b.f_lot_n  
union all
select '    '  
     , b.f_lot_n
     , 0
     , $0.00
     , sum(b.f_inv_q)      
     , sum(b.f_inv_a)      
  from d280db1.dbo.t280lot  b                                                   
     , d280db1.dbo.t280org  c                                                   
 where b.f_fypd      = @epd
   and c.f_afm_c     = b.f_afm_c
   and c.f_afm_c     like @afm
   and c.f_grp_x     like @grp
   and c.f_div_x     like @dvn
   and c.f_bu_x      like @bus
   and b.f_lot_n     like @lot
   and b.i_nsid_c    like @dev
   and b.i_spec_c    like @spc
   and b.f_loc_c     like @loc
   and b.f_div_c     like @div
   and b.f_dpt_c     like @dpt
   and b.f_invtyp_c  like @inv
   and b.f_syssrc_c  like @src
   and b.f_stg_c     like @stg
   and b.f_fop_c     like @fop
   and b.f_afm_c     like @afm
   and b.f_acct_c    like @act
/* and b.f_glval_i   like @glv */
 group by b.f_lot_n  
end

   goto ENDIT

 
ENDIT: 

/************************************************************************
   Return to the calling environment
 ************************************************************************/

return ( 0 )
go
